package doan.chuyennganh.util;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.util.Pair;
import doan.chuyennganh.model.Attendance;
import doan.chuyennganh.model.AttendanceList;
import doan.chuyennganh.model.Student;

public class ListStudentDataSource {
	public String TABLE_NAME = "tblClass";
	public static final String COLUMN_MSSV = "_id";
	public static final String COLUMN_TEN = "name";
	public static final String COLUMN_LOP = "class";
	private int dbVersion;
	private ArrayList<Student> listStudent = null;
	// Database creation sql statement
	private String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
			+ "(" + ListStudentDataSource.COLUMN_MSSV + " TEXT PRIMARY KEY, "
			+ ListStudentDataSource.COLUMN_TEN + " TEXT, "
			+ ListStudentDataSource.COLUMN_LOP + " TEXT " + ")";

	// Database fields
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	//private Context context;
	private Set<String> tableNames = new HashSet<String>();
	private String[] allColumns = { ListStudentDataSource.COLUMN_MSSV,
			ListStudentDataSource.COLUMN_TEN, ListStudentDataSource.COLUMN_LOP };

	public ListStudentDataSource(Context context, String tableName) {
		TABLE_NAME = "tbl_" + tableName;
		//this.context = context;
		CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
				+ ListStudentDataSource.COLUMN_MSSV + " TEXT PRIMARY KEY, "
				+ ListStudentDataSource.COLUMN_TEN + " TEXT, "
				+ ListStudentDataSource.COLUMN_LOP + " TEXT " + ")";
		SharedPreferenceHelper pre = new SharedPreferenceHelper(context);
		dbVersion = pre.getInt(Constants.DATABASE_VERSION, 1);
		tableNames = pre.getStringSet(Constants.DATABASE_TABLES,
				new HashSet<String>());
		if (!tableNames.contains(TABLE_NAME)) {
			dbVersion += 1;
			tableNames.add(TABLE_NAME);
			pre.putInt(Constants.DATABASE_VERSION, dbVersion);
			pre.putStringSet(Constants.DATABASE_TABLES, tableNames);
		}
		Log.d("DBVERSION", dbVersion + "");
		dbHelper = new DatabaseHelper(context, TABLE_NAME, CREATE_TABLE,
				dbVersion);

	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
		database.enableWriteAheadLogging();
		listStudent = (ArrayList<Student>) getAllStudents();
	}

	public void openForReadOnly() {
		database = dbHelper.getReadableDatabase();
		listStudent = (ArrayList<Student>) getAllStudents();
	}

	public void close() {
		dbHelper.close();
	}

	public long insertStudent(Student student) {
		Student s = getStudent(student.getMaSV());
		if (s != null) {
			return -1;
		}
		ContentValues values = new ContentValues();
		values.put(ListStudentDataSource.COLUMN_MSSV, student.getMaSV());
		values.put(ListStudentDataSource.COLUMN_TEN, student.getHoten());
		values.put(ListStudentDataSource.COLUMN_LOP, student.getLop());
		return database.insert(TABLE_NAME, null, values);
		// long insertId = database.insert(DatabaseHelper.TABLE_NAME, null,
		// values);
		// Cursor cursor = database.query(DatabaseHelper.TABLE_NAME,
		// allColumns, DatabaseHelper.COLUMN_MSSV + " = " + insertId, null,
		// null, null, null);
		// cursor.moveToFirst();
		// Student newStudent = cursorToStudent(cursor);
		// cursor.close();
		// return newStudent;
	}

	public void deleteStudent(Student student) {
		String id = student.getMaSV();
		if (getStudent(student.getMaSV()) == null) {
			return;
		}
		database.delete(TABLE_NAME, COLUMN_MSSV + " = " + id, null);
	}

	public void deleteStudent(String studentID) {
		if (getStudent(studentID) == null) {
			return;
		}
		database.delete(TABLE_NAME, COLUMN_MSSV + " = " + studentID, null);
	}

	private Student getStudent(String id) {
		if (listStudent == null) {
			return null;
		}
		for (Student st : listStudent) {
			if (st.getMaSV().equalsIgnoreCase(id)) {
				return st;
			}
		}
		return null;
	}

	public int updateStudent(Student student) {
		if (getStudent(student.getMaSV()) == null) {
			return -1;
		}
		ContentValues cv = new ContentValues();
		cv.put(ListStudentDataSource.COLUMN_TEN, student.getHoten());
		cv.put(ListStudentDataSource.COLUMN_LOP, student.getLop());
		return database.update(TABLE_NAME, cv, COLUMN_MSSV + " LIKE ?",
				new String[] { String.valueOf(student.getMaSV()) });
	}

	public int updateBooleanValue(String id, String column, boolean value) {
		if (getStudent(id) == null) {
			return -1;
		}
		// ContentValues cv = new ContentValues();
		// cv.put(column, value);
		// return database.update(TABLE_NAME, cv,
		// ListStudentDataSource.COLUMN_MSSV + " LIKE ?",
		// new String[] { String.valueOf(id) });
		String UPDATE = "UPDATE " + TABLE_NAME + " SET " + column + " = "
				+ value + " WHERE " + COLUMN_MSSV + " = " + id;
		database.execSQL(UPDATE);
		return 1;
	}

	public int updateIntValue(String id, String column, int value) {
		Student stu = getStudent(id);
		if (stu == null) {
			return -1;
		}

		int cols = 0;
		ContentValues cv = new ContentValues();
		cv.put(column, value);
		cols = database.update(TABLE_NAME, cv, COLUMN_MSSV + " = " + id, null);
		return cols;
	}

	public List<Student> getAllStudents() {
		List<Student> Students = new ArrayList<Student>();

		Cursor cursor = database.query(TABLE_NAME, allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Student Student = cursorToStudent(cursor);
			Students.add(Student);
			cursor.moveToNext();
		}
		// make sure to close the cursor
		cursor.close();
		return Students;
	}

	public void insertColumn(String new_col, int defaultValue) {
		if (new_col == null || new_col == "") {
			return;
		}
		
		Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " LIMIT 0", null);
		Log.d("COLUMNS", cursor.getColumnNames().toString());
		String[] colNames = cursor.getColumnNames();

		boolean isColumnExist = false;
		for (String col : colNames) {
			if (col.equalsIgnoreCase(new_col)) {
				isColumnExist = true;
				break;
			}
		}

		if (!isColumnExist) {
//			dbVersion++;
//			SharedPreferenceHelper pre = new SharedPreferenceHelper(context);
//			pre.putInt(Constants.DATABASE_VERSION, dbVersion);
//			dbHelper = new DatabaseHelper(context, TABLE_NAME, CREATE_TABLE,
//					dbVersion);
//			String old_table = "OLD_"+TABLE_NAME;
//			String RENAME = "ALTER TABLE " + TABLE_NAME + " RENAME TO " +  old_table;
//			database.execSQL(RENAME);
			String INSERT_COLUMN = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN "
					+ new_col + " INTEGER DEFAULT "+ defaultValue;
			database.execSQL(INSERT_COLUMN);
//			CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
//					+ ListStudentDataSource.COLUMN_MSSV + " TEXT PRIMARY KEY, "
//					+ ListStudentDataSource.COLUMN_TEN + " TEXT, "
//					+ ListStudentDataSource.COLUMN_LOP + " TEXT,"
//					+ new_col + " INTEGER DEFAULT 0 )";
//			database.execSQL(CREATE_TABLE);
//			String INSERT = "INSERT INTO " + TABLE_NAME + " SELECT * FROM " + old_table;
//			database.execSQL(INSERT);
//			database.execSQL("DROP TABLE IF EXISTS " + old_table);
		}

		// for (String ms : checkList) {
		// updateIntValue(ms, new_col, period);
		// }
	}

	private Student cursorToStudent(Cursor cursor) {
		Student student = new Student();
		student.setMaSV(cursor.getString(0));
		student.setHoten(cursor.getString(1));
		student.setLop(cursor.getString(2));
		return student;
	}

	private Attendance cursorToAttendance(Cursor cursor) {
		if (listStudent == null) {
			return null;
		}
		Attendance attendance = new Attendance();
		cursor.moveToFirst();
		attendance.setDate(cursor.getColumnName(0));
		int i = 0;
		while (!cursor.isAfterLast()) {
			int period = cursor.getInt(0);
			String id = listStudent.get(i).getMaSV();
			attendance.add(new Pair<String, Integer>(id, period));
			cursor.moveToNext();
			i++;
		}
		cursor.close();
		return attendance;
	}

	public String[] getAllColumnsName() {
		Cursor dbCursor = database.query(TABLE_NAME, null, null, null, null,
				null, null);
		String[] columnNames = dbCursor.getColumnNames();
		return columnNames;
	}

	public AttendanceList getAllttendances() {
		AttendanceList res = new AttendanceList();
		if (listStudent == null) {
			listStudent = (ArrayList<Student>) getAllStudents();
		}
		if (listStudent == null || listStudent.size() <= 0) {
			return res;
		}
		String columns[] = getAllColumnsName();
		for (int i = 3; i < columns.length; i++) {
			Cursor cursor = database.query(TABLE_NAME,
					new String[] { columns[i] }, null, null, null, null, null);
			res.add(cursorToAttendance(cursor));
		}

		return res;
	}

}
